﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using FarPoint.Web.Spread;
using System.Drawing;
using System.Data.Common;
using System.Data;
using FarPoint.Web.Spread.Model;

namespace ControlExplorer.samples.OrderEntry
{
  public partial class MaintainDB : System.Web.UI.Page
  {
    DataSet DataSetPubs1 = new DataSet();
    StyleInfo style = new StyleInfo();
    string sqlConString;

    private void Page_Load(System.Object sender, System.EventArgs e)
    {
      //Put user code to initialize the page here
      if (!Page.IsPostBack)
      {
        sqlConString = (string)Session["fpdbpath"];

        FillDataSet();
        InitSpread();
        this.FpSpread1.UseClipboard = false;
      }

      if (this.InIE9PlusCompatView())
      {
        foreach (FarPoint.Web.Spread.FpSpread spread in this.GetSpreads(this))
        {
          FarPoint.Web.Spread.SheetView activeSheet = spread.ActiveSheetView;
          FarPoint.Web.Spread.Border border = activeSheet.DefaultStyle.Border;
          if (border == null || border.IsEmpty)
          {
            activeSheet.DefaultStyle.Border = new FarPoint.Web.Spread.Border(BorderStyle.Solid, activeSheet.GridLineColor, 1);
          }
        }
      }

    }

    private bool InIE9PlusCompatView()
    {
      if (this.Page.Request.Browser.MajorVersion == 7)
      {
        string userAgent = this.Page.Request.UserAgent;
        int index = userAgent.IndexOf("trident/", StringComparison.OrdinalIgnoreCase);
        int index2 = 0;
        if (index >= 0)
        {
          index2 = userAgent.IndexOf(";", index);
        }
        else
        {
          index2 = -1;
        }
        if (index2 > index && index >= 0)
        {
          double tridentVersion = 0;
          if (double.TryParse(userAgent.Substring(index + 8, index2 - (index + 8)), out tridentVersion))
          {
            return tridentVersion >= 5;
          }
        }
      }
      return false;
    }

    private System.Collections.IEnumerable GetSpreads(Control ctrl)
    {
      System.Collections.Generic.List<FarPoint.Web.Spread.FpSpread> list = new System.Collections.Generic.List<FarPoint.Web.Spread.FpSpread>();
      foreach (Control c in ctrl.Controls)
      {
        foreach (FarPoint.Web.Spread.FpSpread spread in GetSpreads(c))
        {
          list.Add(spread);
        }

        if (c is FarPoint.Web.Spread.FpSpread)
        {
          list.Add((FarPoint.Web.Spread.FpSpread)c);
        }
      }

      return list;
    }

    public void InitSpread()
    {
      int i = 0;

      //Add data to Spread

      FpSpread1.Sheets[0].DataModel = new DefaultSheetDataModel(DataSetPubs1, "书名");
      FpSpread1.Sheets[1].DataModel = new DefaultSheetDataModel(DataSetPubs1, "商店");
      FpSpread1.Sheets[2].DataModel = new DefaultSheetDataModel(DataSetPubs1, "出版社");

      FpSpread1.Sheets[0].SheetName = "书名";
      FpSpread1.Sheets[1].SheetName = "商店";
      FpSpread1.Sheets[2].SheetName = "出版社";

      //Book Title
      FpSpread1.Sheets[0].ColumnHeader.RowCount = 2;
      FpSpread1.Sheets[0].ColumnHeaderSpanModel.Add(0, 0, 1, 6);
      FpSpread1.Sheets[0].SetColumnLabel(0, 0, "书名");

      FpSpread1.Sheets[0].SetColumnLabel(1, 0, "ID");
      FpSpread1.Sheets[0].SetColumnLabel(1, 1, "书名");
      FpSpread1.Sheets[0].SetColumnLabel(1, 2, "种类");
      FpSpread1.Sheets[0].SetColumnLabel(1, 3, "价格");
      FpSpread1.Sheets[0].SetColumnLabel(1, 4, "出版日期");
      FpSpread1.Sheets[0].SetColumnLabel(1, 5, "注释");

      FpSpread1.Sheets[0].SetColumnWidth(0, 60);
      FpSpread1.Sheets[0].SetColumnWidth(1, 375);
      FpSpread1.Sheets[0].SetColumnWidth(2, 100);
      FpSpread1.Sheets[0].SetColumnWidth(3, 65);
      FpSpread1.Sheets[0].SetColumnWidth(4, 85);
      FpSpread1.Sheets[0].SetColumnWidth(5, 150);

      //Stores
      FpSpread1.Sheets[1].ColumnHeader.RowCount = 2;
      FpSpread1.Sheets[1].ColumnHeaderSpanModel.Add(0, 0, 1, 6);
      FpSpread1.Sheets[1].SetColumnLabel(0, 0, "商店");

      FpSpread1.Sheets[1].SetColumnLabel(1, 0, "ID");
      FpSpread1.Sheets[1].SetColumnLabel(1, 1, "商店名");
      FpSpread1.Sheets[1].SetColumnLabel(1, 2, "地址");
      FpSpread1.Sheets[1].SetColumnLabel(1, 3, "城市");
      FpSpread1.Sheets[1].SetColumnLabel(1, 4, "州");
      FpSpread1.Sheets[1].SetColumnLabel(1, 5, "邮编");

      FpSpread1.Sheets[1].SetColumnWidth(0, 50);
      FpSpread1.Sheets[1].SetColumnWidth(1, 270);
      FpSpread1.Sheets[1].SetColumnWidth(2, 175);
      FpSpread1.Sheets[1].SetColumnWidth(3, 85);
      FpSpread1.Sheets[1].SetColumnWidth(4, 50);
      FpSpread1.Sheets[1].SetColumnWidth(5, 60);


      //Publishers
      FpSpread1.Sheets[2].ColumnHeader.RowCount = 2;
      FpSpread1.Sheets[2].ColumnHeaderSpanModel.Add(0, 0, 1, 5);
      FpSpread1.Sheets[2].SetColumnLabel(0, 0, "出版社");

      FpSpread1.Sheets[2].SetColumnLabel(1, 0, "ID");
      FpSpread1.Sheets[2].SetColumnLabel(1, 1, "姓名");
      FpSpread1.Sheets[2].SetColumnLabel(1, 2, "城市");
      FpSpread1.Sheets[2].SetColumnLabel(1, 3, "州");
      FpSpread1.Sheets[2].SetColumnLabel(1, 4, "国家");

      FpSpread1.Sheets[2].SetColumnWidth(0, 50);
      FpSpread1.Sheets[2].SetColumnWidth(1, 250);
      FpSpread1.Sheets[2].SetColumnWidth(2, 100);
      FpSpread1.Sheets[2].SetColumnWidth(3, 50);
      FpSpread1.Sheets[2].SetColumnWidth(4, 75);

      //Spread Default Style
      ISheetStyleModel fpmodel = new DefaultSheetStyleModel(100, 6);
      for (i = 0; i <= 2; i++)
      {
        //fpmodel.SetAlternatingBackColor(0, Color.Linen) '.PaleGoldenrod)

        FpSpread1.Sheets[i].StyleModel = fpmodel;
        style = new StyleInfo();
        style.Reset();
        style.Font.Size = FontUnit.Point(10);
        style.Font.Name = "Verdana";
        FpSpread1.Sheets[i].SetStyleInfo(-1, -1, style);


      }


      SetStyleCurrency(0, 3);
      //Price
      SetStyleDate(0, 4);
      //date

    }

    public void SetStyleDate(int sheetnum, int col)
    {
      style = new StyleInfo();
      style.Reset();
      DateTimeCellType dt = new DateTimeCellType("mm/dd/yyyy");
      dt.FormatString = "d";
      style.CellType = dt;
      FpSpread1.Sheets[sheetnum].SetStyleInfo(-1, col, style);

    }

    public void SetStyleCurrency(int sheetnum, int col)
    {
      style = new StyleInfo();
      style.Reset();
      CurrencyCellType curType = new CurrencyCellType();
      style.CellType = curType;
      FpSpread1.Sheets[sheetnum].SetStyleInfo(-1, col, style);

    }

    public void FillDataSet()
    {
      DbProviderFactory factory = null;
      DbConnection connection = DBHelper.CreateConnection(sqlConString, out factory);
      DbCommand dbCommand = connection.CreateCommand();
      DbDataAdapter adapter1 = factory.CreateDataAdapter();
      DbDataAdapter adapter2 = factory.CreateDataAdapter();
      DbDataAdapter adapter3 = factory.CreateDataAdapter();

      connection.Open();

      dbCommand.CommandText = "SELECT title_id,title,type,price,pubdate,notes FROM Titles";
      adapter1.SelectCommand = dbCommand;
      adapter1.Fill(DataSetPubs1, "Titles");

      dbCommand.CommandText = "SELECT stor_id,stor_name,stor_address,city,state,zip FROM Stores";
      adapter2.SelectCommand = dbCommand;
      adapter2.Fill(DataSetPubs1, "Stores");

      dbCommand.CommandText = "SELECT pub_id, pub_name, city, state, country FROM publishers";
      adapter3.SelectCommand = dbCommand;
      adapter3.Fill(DataSetPubs1, "Publishers");

      connection.Close();
    }
  }
}